#!/usr/bin/env python
# -*- coding: UTF-8 -*-
# pylint: disable-msg = C0301
"""Script to convert a kanjidic2 xml file into an sqlite db"""

from __future__ import division
import sqlite3
from lxml import etree
import sys
import os

__author__ = "Durand D'souza"
__copyright__ = "Copyright 2010, Kuerī"
__license__ = "GPL"
__version__ = "0.1"
__email__ = "durand1[@]gmail[.]com"

try:
    # Get the input xml and output db from commandline arguments
    KANJIDIC = sys.argv[1]
    SQLITEDB = sys.argv[2]
    if not os.path.exists(KANJIDIC):
        raise
except:
    print "Usage: python kanjidic2sqlite.py [KANJIDIC XML] [SQLITE DB TO OUTPUT]"
    sys.exit()

# Check if the db exists, if it does, complain because we need an empty one

if os.path.exists(SQLITEDB):
    print "A file already exists at " + SQLITEDB
    print "Please change the location of the sql database as we need a clean one."
    sys.exit()
# CREATE a connection to the db
CONNECTION = sqlite3.connect(SQLITEDB)
# Start a CURSOR
CURSOR = CONNECTION.cursor()

try:
    # Make a primary table containing the literal (primary key), grade, stroke_count, frequency and jlpt level
    CURSOR.execute('''CREATE TABLE main (literal text primary key, grade integer, stroke_count integer, freq integer, jlpt integer)''')

    # Make a secondary table containing code point info
    CURSOR.execute('''CREATE TABLE codepoint (literal text, cp_type text, cp_value text)''')

    # Make another secondary table containing radical info
    CURSOR.execute('''CREATE TABLE radical (literal text, rad_type text, rad_value integer)''')

    # Make a table containing variants
    CURSOR.execute('''CREATE TABLE variants (literal text, var_type text, variant text)''')

    # Make a table containing dict references
    CURSOR.execute('''CREATE TABLE dicts (literal text, dr_type text, dic_reference text)''')

    # Make a table containing query codes
    CURSOR.execute('''CREATE TABLE query_codes (literal text, qc_type text, q_code text)''')

    # Make a table containing readings
    CURSOR.execute('''CREATE TABLE readings (literal text, r_type text, reading text)''')

    # Make a table containing meanings
    CURSOR.execute('''CREATE TABLE meanings (literal text, m_lang text, meaning text)''')

    # Make a table containing nanori
    CURSOR.execute('''CREATE TABLE nanori (literal text, nanori text)''')
except sqlite3.OperationalError:
    pass

# Parse the xml file into something useful
ROOT = etree.parse(KANJIDIC).getroot()

# The first item is the initial comment, so we ignore that
ROOT = ROOT[1:]

COUNTER = 0
TOTAL_KANJI = len(ROOT)
# We then loop through each kanji character and add their data to the database
for i in range(1, TOTAL_KANJI, 2):
    for info in ROOT[i]:
        if info.tag == "literal":
            # Insert the literal
            CURSOR.execute("INSERT INTO main (literal) VALUES ('%s')"%(info.text))
            kanji = info.text
            
        elif info.tag == "codepoint":
            # Go through each code point and add them to the codepoint table
            for codepoint in info:
                CURSOR.execute("INSERT INTO codepoint VALUES (?, ?, ?)", (kanji, codepoint.get('cp_type'), codepoint.text))
                
        elif info.tag == "radical":
            # Go through each radical and add them to the radical table
            for radical in info:
                CURSOR.execute("INSERT INTO radical VALUES (?, ?, ?)", (kanji, radical.get('rad_type'), int(radical.text)))
                
        elif info.tag == "misc":
            # This tag contains a lot of info including grade, stroke_count, variants, freq and jlpt
            for misc_info in info:
                if misc_info.tag == "grade":
                    # Update the database with grade info
                    CURSOR.execute("UPDATE main SET grade = ? WHERE literal = ?", (int(misc_info.text), kanji))
                    
                elif misc_info.tag == "stroke_count":
                    # Update the database with stroke_count info
                    CURSOR.execute("UPDATE main SET stroke_count = ? WHERE literal = ?", (int(misc_info.text), kanji))
                    
                elif misc_info.tag == "variant":
                    # Add the variants to the variants table
                    CURSOR.execute("INSERT INTO variants VALUES (?, ?, ?)", (kanji, misc_info.get('var_type'), misc_info.text))
                    
                elif misc_info.tag == "freq":
                    # Update the database with freq info
                    CURSOR.execute("UPDATE main SET freq = ? WHERE literal = ?", (int(misc_info.text), kanji))
                    
                elif misc_info.tag == "jlpt":
                    # Update the database with jlpt info
                    CURSOR.execute("UPDATE main SET jlpt = ? WHERE literal = ?", (int(misc_info.text), kanji))
                    
                    
        elif info.tag == "dic_number":
            # There are lots of different dicts referenced here so add them all to the dicts table
            for dicts in info:
                # If there is no m_vol setting, then just add the basic info
                if dicts.get('m_vol', None) == None:
                    dic_reference = dicts.text
                else:
                    dic_reference = dicts.text + " vol:" + dicts.get('m_vol') + " page:" + dicts.get('m_page')
                CURSOR.execute("INSERT INTO dicts VALUES (?, ?, ?)", (kanji, dicts.get('dr_type'), dic_reference))
                
        elif info.tag == "query_code":
            # There are lots of query codes, so add them all to the query_codes table
            for query_code in info:
                CURSOR.execute("INSERT INTO query_codes VALUES (?, ?, ?)", (kanji, query_code.get('qc_type'), query_code.text))
        
        elif info.tag == "reading_meaning":
            # This tag has two groups, rmgroup and nanori
            for group in info:
                if group.tag == "rmgroup":
                    # Get all the readings and add them to the readings or meanings tables
                    for reading_meaning in group:
                        if reading_meaning.tag == "reading":
                            # Add it to the readings table
                            CURSOR.execute("INSERT INTO readings VALUES (?, ?, ?)", (kanji, reading_meaning.get('r_type'), reading_meaning.text))
                        elif reading_meaning.tag == "meaning":
                            # Add it to the meanings table
                            CURSOR.execute("INSERT INTO meanings VALUES (?, ?, ?)", (kanji, reading_meaning.get('m_lang',"en"), reading_meaning.text))
                elif group.tag == "nanori":
                    # Add it to the nanori table
                    CURSOR.execute("INSERT INTO nanori VALUES (?, ?)", (kanji, group.text))
    COUNTER += 1
    print str(COUNTER) + "/" + str(int(TOTAL_KANJI/2)) + "(%.2f"%(COUNTER/(TOTAL_KANJI/2)*100) + "%)"

try:
    # Add another to search against grade, stroke, freq and jlpt
    CURSOR.execute('''CREATE INDEX main_index ON main (literal, grade, stroke_count, freq, jlpt)''')
    # Add an index to search against readings and kanji
    CURSOR.execute('''CREATE INDEX readings_index ON readings (literal, r_type, reading)''')
    # Add an index to search against kanji and meanings
    CURSOR.execute('''CREATE INDEX meanings_index ON meanings (literal, m_lang, meaning)''')
except sqlite3.OperationalError:
    pass

CONNECTION.commit()